Data Transformation and Relationships 9
Step 1: Click on 'Transform Data' to open Power Query Editor (See screenshot)
Step 2: Right click on the Start_Time column then click 'Duplicate Column' (See GIF below)
Step 3: Right click on 'Start_time - Copy', scroll down to 'Transform', then click on 'Time Only'. Scroll across to locate 'Start_Time, right click on 'Start_Time, scroll down to 'Transform', then click on 'Date Only' (See GIF below).
Step 4: Next step is to Group Severity. Severity is grouped from 0 - 5. For this Data, we will group 0,1 as Low Severity, 2 as Average Severity and 3,4 as High Severity.
Step 5: Now it's time to clean our Data and Remove unused columns.
Click 'Close & Apply' after removing all unused columns.
Step 6: Click on the ellipses for the US_Accidents_Dec20 Table then click on New column. Copy and paste DAX equation below, then click (See screenshot and GIF below)
TimeKey = FORMAT(US_Accidents_Dec20[Start_Time], "hhmm")Step 7: Now it's time to create relationships between both tables. Click on the Model icon to show Data Model. Select 'Date' column on the 'Date' Table then drag to the 'Start_Date' column on the 'US_Accident_Dec20' table. Select 'TimeKey' on the 'Time' table the drag to 'TimeKey' on the 'US_Accident_Dec20' table. (See GIF below).
Clean, transform, and load data in Power BI: https://docs.microsoft.com/en-us/learn/modules/clean-data-power-bi/
Create and manage relationships in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships